#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/3/31 14:16
# @Author : 闫峰
# @File    : db_pool.py
import json
from datetime import datetime
import pymysql

from common.setting import ensure_path_sep
from util.readFileUtils.yamlControl import GetYamlData

from dbutils.pooled_db import PooledDB
from typing import Optional, Tuple, List, Dict, Union


class Database:
    def __init__(self, yaml_path, database_key,database):
        """
        yaml_path 数据库配置信息yaml 文件地址
        database_name  yaml文件中的数据库信息 key
        """
        self.mysql = GetYamlData(yaml_path).get_yaml_data()[database_key]
        # 设置连接超时时间（例如：10秒）
        connect_timeout = 9999

        self.pool = PooledDB(
            creator=lambda: pymysql.connect(
                host=self.mysql["host"],
                user=self.mysql["user"],
                password=self.mysql["password"],
                database=database,
                port=int(self.mysql["port"]),
                charset="utf8mb4",
                connect_timeout=connect_timeout,  # 设置超时时间
            ),
            maxconnections=100,
            ping=1,
        )

    def execute(self, sql: str, value: Optional[Tuple] = None) -> int:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        conn = self.pool.connection()
        try:
            with conn.cursor() as cursor:
                if value is None:

                    rows = cursor.execute(sql)
                else:
                    rows = cursor.execute(sql, value)
                conn.commit()
                return rows
        except Exception as e:
            conn.rollback()
            print(f"{sql}update fail")
            # print(f"{str(value)}更新失败")
            raise e
        finally:
            conn.close()

    def execute_many(self, sql: str, value: list) -> int:
        value = list(value)
        # 检查SQL语句和值的类型
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if not isinstance(value, list):
            raise ValueError("The value parameter must be a list.")

        # 连接池获取连接
        conn = self.pool.connection()
        try:
            # 使用分批的方式执行插入，每批200条数据
            batch_size = 20
            num = 0
            for i in range(0, len(value), batch_size):
                batch_values = value[i:i + batch_size]
                with conn.cursor() as cursor:
                    rows = cursor.executemany(sql, batch_values)
                    # print(f"影响了{rows}行")
                    num += rows
                conn.commit()
            return num  # 返回插入的总记录数
        except Exception as e:
            conn.rollback()
            print(sql)
            print(batch_values)
            print(e)
            raise e
        finally:
            conn.close()  # 确保连接最终被关闭

    def fetch_all(self, sql: str, value: Optional[Tuple] = None) -> List[Dict]:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        conn = self.pool.connection()
        result = []
        try:
            with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
                if not value:
                    cursor.execute(sql)
                else:
                    cursor.execute(sql, value)
                result = cursor.fetchall()
                # for res in result:
                #     self.verify(res)
                return result
        except Exception as e:
            print(sql)
            print(value)

            raise e
            pass
        finally:
            conn.close()

    def fetch_all_tuple(self, sql: str, value: Optional[Tuple] = None) -> List[Dict]:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        if value is not None and not isinstance(value, tuple):
            raise ValueError("The value parameter must be a tuple or None.")

        conn = self.pool.connection()
        try:
            with conn.cursor() as cursor:
                if not value:
                    cursor.execute(sql)
                else:
                    cursor.execute(sql, value)
                result = cursor.fetchall()
                # for res in result:
                #     self.verify(res)
                return result
        except Exception as e:
            print(sql)
            print(value)
            raise e
            pass
        finally:
            conn.close()

    def verify(self, result: dict) -> Union[dict, None]:
        """验证结果能否被json.dumps序列化"""
        # 尝试变成字符串，解决datetime 无法被json 序列化问题
        try:
            json.dumps(result)
        except TypeError:  # TypeError: Object of type datetime is not JSON serializable
            for k, v in result.items():
                if isinstance(v, datetime):
                    result[k] = str(v)[0:10]
        # return result

    def truncate(self, sql):
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        conn = self.pool.connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.commit()
            conn.close()

    def fetch_max_id(self, sql: str) -> int:
        if not isinstance(sql, str):
            raise ValueError("The sql parameter must be a string.")
        conn = self.pool.connection()
        result = []
        try:
            with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:

                cursor.execute(sql)
                result = cursor.fetchall()

            return result
        except Exception as e:
            print(sql)
            raise e


if __name__ == '__main__':
    zhcxkj_center_category_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
    insert_sql = f"""
                    INSERT INTO test_zhcxkj_center_strategy_comprehensive.t_allegro_listing_filter_category
                    (`listing_template_id`, `category_id`)
                    VALUES
                    (39,%s);
                    """
    data_list = [50696, 50698, 47631, 255504, 253970, 317970, 46100, 68630, 68631, 111641, 541, 64542, 321567, 68645,
                 123429, 255525, 64555, 64557, 123448, 123450, 259644, 254019, 77893, 46156, 12365, 111696, 256081,
                 256082, 112724, 112725, 260696, 13407, 45664, 15979, 45682, 320626, 110196, 312437, 256628, 15990,
                 320632, 45689, 258173, 15997, 128, 16002, 260738, 16008, 318603, 124044, 258188, 261777, 255633,
                 255635, 257172, 308881, 251542, 261271, 253585, 124059, 256156, 261283, 257191, 19628, 319667, 316085,
                 61110, 110773, 253624, 261306, 316091, 147646, 49855, 261829, 15557, 49864, 85193, 260812, 251086,
                 11984, 260308, 121558, 260311, 320728, 147673, 85211, 257755, 147678, 26847, 259808, 259811, 300773,
                 259305, 5354, 256748, 314605, 111856, 122609, 126194, 5365, 260855, 306429, 28413, 121599, 84735,
                 320257, 121603, 76040, 318735, 252689, 302354, 88337, 261402, 54045, 147742, 260896, 54049, 110883,
                 88357, 312613, 110887, 54054, 54057, 315690, 86318, 316724, 54068, 125748, 20276, 319802, 110907,
                 110908, 110910, 110911, 110912, 110913, 27971, 110916, 318277, 320326, 27975, 110920, 254280, 303945,
                 110922, 316748, 316751, 90026, 68951, 110940, 82269, 19808, 314209, 110944, 110945, 300389, 122726,
                 314725, 253799, 76652, 253806, 148335, 26991, 122739, 122743, 318328, 319352, 105336, 122747, 320380,
                 260473, 105340, 122751, 93056, 122746, 122752, 105343, 105339, 312709, 261000, 122765, 47502, 121237,
                 28057, 18842, 319899, 1437, 319902, 98726, 261544, 77737, 313721, 90027, 257452, 300461, 98734, 261548,
                 89008, 98737, 90032, 321971, 5554, 319926, 122807, 257465, 90043, 257987, 90054, 257478, 90056, 98761,
                 98762, 18891, 318413, 319950, 93136, 90065, 1491, 1494, 28118, 19416, 1497, 1495, 148443, 308701,
                 93663, 122847, 28129, 90081, 111075, 90083, 90085, 90087, 322030, 259567, 257008, 261619, 260596,
                 306165, 261627]

    zhcxkj_center_category_db.execute_many(insert_sql, data_list)
